﻿/**
//  ScheduleEntity.cs
//  Marquee Moment
//  Copyright 2011 Systematix Technocrates. All rights reserved.
**/
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using NLog;


namespace MarqueeMoment.Entities
{
    /// <summary>
    /// This is the class to keep all the transaction method from tblSchedule
    /// </summary>
    public class ScheduleEntity
    {
        private static Logger logger = LogManager.GetCurrentClassLogger();
        #region Fields

        private Int32 scheduleId;
        private Int32 imageId;
        private DateTime scheduleTime;

        private List<ScheduleEntity> schedulelist;

        #endregion Fields

        #region Constructors

        public ScheduleEntity()
            : base()
        {
            scheduleId = 0;
            imageId = 0;
            var timeZone = TimeZoneInfo.FindSystemTimeZoneById("Eastern Standard Time");
            scheduleTime = TimeZoneInfo.ConvertTime(DateTime.Now, timeZone);

            schedulelist = new List<ScheduleEntity>();
        }


        #endregion Constructors

        #region Properties

        public Int32 ScheduleId
        {
            get { return scheduleId; }
            set { scheduleId = value; }
        }

        public Int32 ImageId
        {
            get { return imageId; }
            set { imageId = value; }
        }


        public DateTime ScheduleTime
        {
            get { return scheduleTime; }
            set { scheduleTime = value; }
        }

        #endregion Properties

        #region Methods

        #region Public

        #endregion Public

        #region Private

        #endregion Private

        #endregion Methods

        #region Static methods

        #region Public

        /// <summary>
        /// To select all the records from the tblSchedule table
        /// </summary>
        /// <returns></returns>     
        public static List<ScheduleEntity> SelectAll()
        {
            List<ScheduleEntity> schedulelist = null;
            ScheduleEntity schedule = null;
            #region Initialization
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = Connection.GetConnection();
            if (cmd.Connection == null)
                return null;

            cmd.CommandText = "[spSelectAllSchedule]";
            cmd.CommandType = CommandType.StoredProcedure;
            SqlDataAdapter adapter = new SqlDataAdapter(cmd);
            DataSet dataSet = new DataSet();
            #endregion Initialization

            try
            {
                adapter.Fill(dataSet);
                if (dataSet.Tables.Count > 0)
                    if (dataSet.Tables[0].Rows.Count > 0)
                    {
                        schedulelist = new List<ScheduleEntity>();
                        for (int count = 0; count < dataSet.Tables[0].Rows.Count; count++)
                        {
                            schedule = new ScheduleEntity();
                            schedule.ScheduleId = Convert.ToInt32(dataSet.Tables[0].Rows[count][tblSchedule.ScheduleId]);
                            schedule.ImageId = Convert.ToInt32(dataSet.Tables[0].Rows[count][tblSchedule.ImageId]);
                            schedule.ScheduleTime = Convert.ToDateTime(dataSet.Tables[0].Rows[count][tblSchedule.ScheduleTime]);
                            schedulelist.Add(schedule);
                        }
                    }
            }
            catch (Exception exc)
            {
                logger.Error(exc.StackTrace);
                Dialogs.Error("Error at Select All Schedule Images");
            }
            finally
            {
                if (dataSet != null)
                    dataSet.Dispose();

                Connection.CloseConnection(cmd.Connection);
                cmd.Dispose();
            }
            return schedulelist;
        }

        /// <summary>
        /// To select all the records from tblSchedule and return Dataset of the records
        /// </summary>
        /// <returns></returns>
        public static DataSet SelectAllSchedule()
        {
            #region Initialization
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = Connection.GetConnection();
            if (cmd.Connection == null)
                return null;

            cmd.CommandText = "[spSelectAllSchedule]";
            cmd.CommandType = CommandType.StoredProcedure;
            SqlDataAdapter adapter = new SqlDataAdapter(cmd);
            DataSet dataSet = new DataSet();
            #endregion Initialization

            try
            {
                adapter.Fill(dataSet);
                if (dataSet.Tables.Count > 0)

                    return dataSet;

            }
            catch (Exception exc)
            {
                logger.Error(exc.StackTrace);
                Dialogs.Error("Error at Select All Schedule Images");
            }
            finally
            {
                Connection.CloseConnection(cmd.Connection);
                cmd.Dispose();
            }
            return null;
        }


        /// <summary>
        /// To delete the records of the tblSchedule table
        /// </summary>
        /// <param name="scheduleId">Schedule Id is the Id to delete the Particular Record /param>
        public static void Delete(int scheduleId)
        {
            #region Initialization
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = Connection.GetConnection();
            if (cmd.Connection == null)
                return;

            cmd.CommandText = "[spDeleteSchedule]";
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.AddWithValue("@" + tblSchedule.ScheduleId, scheduleId);
            SqlDataAdapter adapter = new SqlDataAdapter(cmd);
            DataSet dataSet = new DataSet();
            #endregion Initialization

            try
            {
                adapter.Fill(dataSet);
            }
            catch (Exception exc)
            {
                logger.Error(exc.StackTrace);
                Dialogs.Error("Error at Delete Schedule Image");
            }
            finally
            {
                if (dataSet != null)
                    dataSet.Dispose();

                Connection.CloseConnection(cmd.Connection);
                cmd.Dispose();
            }
        }


        /// <summary>
        /// To delete the records of the tblSchedule table
        /// </summary>
        /// <param name="scheduleId">Schedule Id is the Id to delete the Particular Record /param>
        public static void DeleteOldSchedule(DateTime currentDate)
        {
            #region Initialization            
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = Connection.GetConnection();
            if (cmd.Connection == null)
                return;

            cmd.CommandText = "[spDeleteOldSchedule]";
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.AddWithValue("@" + "CurrentDate", currentDate);
            SqlDataAdapter adapter = new SqlDataAdapter(cmd);
            DataSet dataSet = new DataSet();
            #endregion Initialization

            try
            {
                adapter.Fill(dataSet);
            }
            catch (Exception exc)
            {
                logger.Error(exc.StackTrace);
                Dialogs.Error("Error at Delete Schedule Image");
            }
            finally
            {
                if (dataSet != null)
                    dataSet.Dispose();

                Connection.CloseConnection(cmd.Connection);
                cmd.Dispose();
            }
        }

        /// <summary>
        /// To update the record of the tblSchedule
        /// </summary>
        /// <param name="schedule">This contains the schedule Id to update the record</param>
        public static void Update(ScheduleEntity schedule)
        {

            #region Initialization
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = Connection.GetConnection();
            if (cmd.Connection == null)
                return;

            cmd.CommandText = "[spUpdateSchedule]";
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.AddWithValue("@" + tblSchedule.ScheduleId, schedule.ScheduleId);
            cmd.Parameters.AddWithValue("@" + tblSchedule.ScheduleId, schedule.ImageId);
            cmd.Parameters.AddWithValue("@" + tblSchedule.ScheduleTime, schedule.ScheduleTime);

            SqlDataAdapter adapter = new SqlDataAdapter(cmd);
            DataSet dataSet = new DataSet();
            #endregion Initialization

            try
            {
                adapter.Fill(dataSet);

            }
            catch (Exception exc)
            {
                logger.Error(exc.StackTrace);
                Dialogs.Error("Error at Update schedule Images");
            }
            finally
            {
                if (dataSet != null)
                    dataSet.Dispose();

                Connection.CloseConnection(cmd.Connection);
                cmd.Dispose();
            }
        }
        /// <summary>
        /// To insert the record in tblSchedule
        /// </summary>
        /// <param name="schedule">It contains the information to insert the record </param>
        public static void Insert(ScheduleEntity schedule)
        {
            #region Initialization
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = Connection.GetConnection();
            if (cmd.Connection == null)
                return;

            cmd.CommandText = "[spInsertSchedule]";
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.AddWithValue("@" + tblSchedule.ImageId, schedule.ImageId);
            cmd.Parameters.AddWithValue("@" + tblSchedule.ScheduleTime, schedule.ScheduleTime);

            SqlDataAdapter adapter = new SqlDataAdapter(cmd);
            DataSet dataSet = new DataSet();
            #endregion Initialization

            try
            {
                adapter.Fill(dataSet);

            }
            catch (Exception exc)
            {
                logger.Error(exc.StackTrace);
                Dialogs.Error("Error at Insert Schedule Image");
            }
            finally
            {
                if (dataSet != null)
                    dataSet.Dispose();

                Connection.CloseConnection(cmd.Connection);
                cmd.Dispose();
            }
        }


        /// <summary>
        /// To select particular record from tblSchedule and return ScheduleEntity
        /// </summary>
        /// <returns></returns>
        public static ScheduleEntity Select(Int32 scheduleId)
        {
            ScheduleEntity schedule = null;
            #region Initialization
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = Connection.GetConnection();
            if (cmd.Connection == null)
                return null;

            cmd.CommandText = "[spSelectScheduleById]";
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.AddWithValue("@" + tblSchedule.ScheduleId, scheduleId);
            SqlDataAdapter adapter = new SqlDataAdapter(cmd);
            DataSet dataSet = new DataSet();
            #endregion Initialization

            try
            {
                adapter.Fill(dataSet);
                if (dataSet.Tables.Count > 0)
                    if (dataSet.Tables[0].Rows.Count > 0)
                    {
                        schedule = new ScheduleEntity();
                        schedule.ScheduleId = Convert.ToInt32(dataSet.Tables[0].Rows[0][tblSchedule.ScheduleId]);
                        schedule.ImageId = Convert.ToInt32(dataSet.Tables[0].Rows[0][tblSchedule.ImageId]);
                        schedule.ScheduleTime = Convert.ToDateTime(dataSet.Tables[0].Rows[0][tblSchedule.ScheduleTime]);
                    }
            }
            catch (Exception exc)
            {
                logger.Error(exc.StackTrace);
                Dialogs.Error("Error at Select ScheduleEntity");
            }
            finally
            {
                if (dataSet != null)
                    dataSet.Dispose();

                Connection.CloseConnection(cmd.Connection);
                cmd.Dispose();
            }
            return schedule;
        }

        /// <summary>
        /// To select particular record from tblSchedule and return ScheduleEntity
       /// </summary>
        /// <param name="scheduleTime">select record whose scheduleTime in db is equals to scheduleTime</param>
        /// <returns>ScheduleEntity</returns>
        public static ScheduleEntity Select(DateTime scheduleTime,Int32 scheduleId)
        {
            ScheduleEntity schedule = null;
            #region Initialization
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = Connection.GetConnection();
            if (cmd.Connection == null)
                return null;

            DateTime scheduleTime1, scheduleTime2;
            scheduleTime1 = scheduleTime.AddMinutes(1);
            scheduleTime2 = scheduleTime.AddMinutes(-1);

            cmd.CommandText = "[spSelectScheduleByScheduleTime]";
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.AddWithValue("@" + tblSchedule.ScheduleId, scheduleId);
            cmd.Parameters.AddWithValue("@" + tblSchedule.ScheduleTime, scheduleTime);
            cmd.Parameters.AddWithValue("@" + tblSchedule.ScheduleTime + "1", scheduleTime1);
            cmd.Parameters.AddWithValue("@" + tblSchedule.ScheduleTime + "2", scheduleTime2);
            SqlDataAdapter adapter = new SqlDataAdapter(cmd);
            DataSet dataSet = new DataSet();
            #endregion Initialization

            try
            {
                adapter.Fill(dataSet);
                if (dataSet.Tables.Count > 0)
                    if (dataSet.Tables[0].Rows.Count > 0)
                    {
                        schedule = new ScheduleEntity();
                        schedule.ScheduleId = Convert.ToInt32(dataSet.Tables[0].Rows[0][tblSchedule.ScheduleId]);
                        schedule.ImageId = Convert.ToInt32(dataSet.Tables[0].Rows[0][tblSchedule.ImageId]);
                        schedule.ScheduleTime = Convert.ToDateTime(dataSet.Tables[0].Rows[0][tblSchedule.ScheduleTime]);
                    }
            }
            catch (Exception exc)
            {
                logger.Error(exc.StackTrace);
                Dialogs.Error("Error at Select ScheduleEntity");
            }
            finally
            {
                if (dataSet != null)
                    dataSet.Dispose();

                Connection.CloseConnection(cmd.Connection);
                cmd.Dispose();
            }
            return schedule;
        }

        /// <summary>
        /// To select all the Schedule image from tblImage and return Dataset of the records
        /// </summary>
        /// <returns></returns>
        public static DataSet SelectAllScheduleWithImage()
        {
            #region Initialization
            var timeZone = TimeZoneInfo.FindSystemTimeZoneById("Eastern Standard Time");
            DateTime currentDate = TimeZoneInfo.ConvertTime(DateTime.Now, timeZone);

            //Delete old schedule records from db
            DeleteOldSchedule(currentDate);

            SqlCommand cmd = new SqlCommand();
            cmd.Connection = Connection.GetConnection();
            if (cmd.Connection == null)
                return null;

            cmd.CommandText = "[spSelectAllScheduleWithImage]";
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.AddWithValue("@" + "CurrentDate", currentDate);
            SqlDataAdapter adapter = new SqlDataAdapter(cmd);
            DataSet dataSet = new DataSet();
            #endregion Initialization

            try
            {
                adapter.Fill(dataSet);
                if (dataSet.Tables.Count > 0)
                    return dataSet;

            }
            catch (Exception exc)
            {
                logger.Error(exc.StackTrace);
                Dialogs.Error("Error at Select All Schedule With Image");
            }
            finally
            {
                if (dataSet != null)
                    dataSet.Dispose();

                Connection.CloseConnection(cmd.Connection);
                cmd.Dispose();
            }
            return null;
        }


        #endregion Public

        #endregion Static methods
    }
}
